﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class LopMonHocRepository : ILopMonHocRepository
    {

        public DTO.LopMonHocDTO GetLopMonHocById(Guid id)
        {
            var maLopParameter = DBParameterFactory.Create("LopMonHocId",id);
            var giangVienIdParameter = DBParameterFactory.Create("GiangVienId", SqlDbType.UniqueIdentifier, DBNull.Value);
            var maHocKyParameter = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            var daPhanHoiParameter = DBParameterFactory.Create("daPhanHoi", SqlDbType.Bit, DBNull.Value);
            var giangVienXacNhanParameter = DBParameterFactory.Create("GiangVienXacNhan", SqlDbType.Bit, DBNull.Value);
            var dongYMoLopParameter = DBParameterFactory.Create("DongYMoLop", SqlDbType.Bit, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPMONHOCSelect]", maLopParameter, giangVienIdParameter, maHocKyParameter, giangVienXacNhanParameter, daPhanHoiParameter, dongYMoLopParameter);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLopMonHocDTO();
        }

        public IEnumerable<DTO.LopMonHocDTO> GetAllLopMonHoc(Guid? maHocKy, Guid? maGiangVien, bool? giangVienXacNhan, bool? daPhanHoi, bool? dongYMoLop)
        {
            List<LopMonHocDTO> lopMonHocList = new List<LopMonHocDTO>();
            var maLopMonHocParameter = DBParameterFactory.Create("LopMonHocId", SqlDbType.UniqueIdentifier, DBNull.Value);
            var giangVienIdParameter = DBParameterFactory.Create("GiangVienId", maGiangVien);
            if (maGiangVien == null)
                giangVienIdParameter = DBParameterFactory.Create("GiangVienId", SqlDbType.UniqueIdentifier, DBNull.Value);
            var maHocKyParameter = DBParameterFactory.Create("MaHocKy", maHocKy);
            if (maHocKy == null)
                maHocKyParameter = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            var giangVienXacNhanParameter = DBParameterFactory.Create("GiangVienXacNhan", giangVienXacNhan);
            if (giangVienXacNhan == null)
                giangVienXacNhanParameter = DBParameterFactory.Create("GiangVienXacNhan", SqlDbType.Bit, DBNull.Value);
            var daPhanHoiParameter = DBParameterFactory.Create("daPhanHoi", daPhanHoi);
            if (daPhanHoi == null)
                daPhanHoiParameter = DBParameterFactory.Create("daPhanHoi", SqlDbType.Bit, DBNull.Value);
            var dongYMoLopParameter = dongYMoLop != null ? DBParameterFactory.Create("DongYMoLop", SqlDbType.Bit, dongYMoLop) : DBParameterFactory.Create("DongYMoLop", SqlDbType.Bit, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPMONHOCSelect]", maLopMonHocParameter, giangVienIdParameter, maHocKyParameter, giangVienXacNhanParameter, daPhanHoiParameter, dongYMoLopParameter);
            var lopMonHocTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in lopMonHocTable)
            {
                lopMonHocList.Add(dataRow.ToLopMonHocDTO());
            }
            return lopMonHocList.AsEnumerable();
        }

        public DTO.LopMonHocDTO InsertLopMonHoc(DTO.LopMonHocDTO lopMonHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPMONHOCInsert]", GetParameterArray(lopMonHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLopMonHocDTO();
        }


        public DTO.LopMonHocDTO UpdateLopMonHoc(DTO.LopMonHocDTO lopMonHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPMONHOCUpdate]", GetParameterArray(lopMonHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLopMonHocDTO();
        }

        public bool DeleteLopMonHoc(Guid lopMonHocId)
        {
            SqlParameter lopMonHocIdParam = new SqlParameter("@lopMonHocId", lopMonHocId);
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_LOPMONHOCDelete]", lopMonHocIdParam);
        }

        #region Private methods

        private System.Data.SqlClient.SqlParameter[] GetParameterArray(LopMonHocDTO lopMonHocDTO)
        {
            SqlParameter[] parameters = new SqlParameter[24];
            parameters[0] = DBParameterFactory.Create("LopMonHocId", lopMonHocDTO.LopMonHocId);
            parameters[1] = DBParameterFactory.Create("MaLopMonHoc", lopMonHocDTO.MaLopMonHoc);
            parameters[2] = DBParameterFactory.Create("MaMonHoc", lopMonHocDTO.MaMonHoc);
            parameters[3] = DBParameterFactory.Create("GiangVien1Id", lopMonHocDTO.MaGiangVien1);
            parameters[4] = DBParameterFactory.Create("GiangVien2Id", lopMonHocDTO.MaGiangVien2);
            if (lopMonHocDTO.MaGiangVien2 == null)
                parameters[4] = DBParameterFactory.Create("GiangVien2Id", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[5] = DBParameterFactory.Create("MaHocKy", lopMonHocDTO.MaHocKy);
            parameters[6] = lopMonHocDTO.MaTietDau != null ? DBParameterFactory.Create("MaTietDau", lopMonHocDTO.MaTietDau) : DBParameterFactory.Create("MaTietDau", SqlDbType.Int, DBNull.Value);
            parameters[17] = lopMonHocDTO.MaTietSau != null ? DBParameterFactory.Create("MaTietSau", lopMonHocDTO.MaTietSau) : DBParameterFactory.Create("MaTietSau", SqlDbType.Int, DBNull.Value);
            parameters[7] = DBParameterFactory.Create("Thu", lopMonHocDTO.Thu);
            parameters[8] = DBParameterFactory.Create("MaPhong", lopMonHocDTO.MaPhong);
            if (lopMonHocDTO.MaPhong == null)
                parameters[8] = DBParameterFactory.Create("MaPhong", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[9] = DBParameterFactory.Create("SiSoToiDa", lopMonHocDTO.SiSoToiDa);
            if (lopMonHocDTO.SiSoToiDa == null)
                parameters[9] = DBParameterFactory.Create("SiSoToiDa", SqlDbType.Int, DBNull.Value);
            parameters[22] = lopMonHocDTO.SiSoThucTe.HasValue ? DBParameterFactory.Create("SiSoThucTe", lopMonHocDTO.SiSoThucTe.Value) : DBParameterFactory.Create("SiSoThucTe", SqlDbType.Int, DBNull.Value);
            parameters[10] = DBParameterFactory.Create("LoaiLop", lopMonHocDTO.LoaiLop);
            parameters[11] = DBParameterFactory.Create("TyLeDiem", lopMonHocDTO.TyLeDiem);
            parameters[12] = DBParameterFactory.Create("MaLopChinh", lopMonHocDTO.MaLopChinh);
            if (lopMonHocDTO.MaLopChinh == null)
                parameters[12] = DBParameterFactory.Create("MaLopChinh", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[13] = DBParameterFactory.Create("GiangVienXacNhan", lopMonHocDTO.GiangVienXacNhan);
            parameters[14] = DBParameterFactory.Create("DaPhanHoi", lopMonHocDTO.DaPhanHoi);
            parameters[15] = DBParameterFactory.Create("GhiChu", lopMonHocDTO.GhiChu);
            if (String.IsNullOrEmpty(lopMonHocDTO.GhiChu))
                parameters[15] = DBParameterFactory.Create("GhiChu", SqlDbType.NVarChar, DBNull.Value);
            parameters[16] = DBParameterFactory.Create("LopSinhVienId", SqlDbType.UniqueIdentifier, lopMonHocDTO.LopSinhVienId);
            if (lopMonHocDTO.LopSinhVienId == null)
                parameters[16] = DBParameterFactory.Create("LopSinhVienId", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[18] = lopMonHocDTO.DongYMoLop != null ? DBParameterFactory.Create("DongYMoLop", SqlDbType.Bit, lopMonHocDTO.DongYMoLop) : DBParameterFactory.Create("DongYMoLop", SqlDbType.Bit, DBNull.Value);
            parameters[19] = lopMonHocDTO.Buoi1 != null ? DBParameterFactory.Create("Buoi1", lopMonHocDTO.Buoi1) : DBParameterFactory.Create("Buoi1", SqlDbType.DateTime, DBNull.Value);
            parameters[20] = lopMonHocDTO.Buoi2 != null ? DBParameterFactory.Create("Buoi2", lopMonHocDTO.Buoi2) : DBParameterFactory.Create("Buoi2", SqlDbType.DateTime, DBNull.Value);
            parameters[21] = lopMonHocDTO.Buoi3 != null ? DBParameterFactory.Create("Buoi3", lopMonHocDTO.Buoi3) : DBParameterFactory.Create("Buoi3", SqlDbType.DateTime, DBNull.Value);
            parameters[23] = !String.IsNullOrEmpty(lopMonHocDTO.Phong) ? DBParameterFactory.Create("Phong", lopMonHocDTO.Phong) : DBParameterFactory.Create("Phong", SqlDbType.NVarChar, DBNull.Value);
            return parameters;
        }
        #endregion

        public LoaiLopDTO GetLoaiLopMonHocById(Guid id)
        {
            SqlParameter maLoaiLopMonHoc = DBParameterFactory.Create("MaLoaiLopMonHoc", id);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOAILOPMONHOCSelect]", maLoaiLopMonHoc);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLoaiLopMonHocDTO();
        }

        public IEnumerable<LoaiLopDTO> GetAllLoaiLopMonHoc()
        {
            IList<LoaiLopDTO> loaiLopList = new List<LoaiLopDTO>();
            SqlParameter maLoaiLopMonHoc = DBParameterFactory.Create("MaLoaiLopMonHoc", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOAILOPMONHOCSelect]", maLoaiLopMonHoc);
            var dataRows = dbResult.Tables[0].Rows;
            foreach (DataRow row in dataRows)
            {
                loaiLopList.Add(row.ToLoaiLopMonHocDTO());
            }
            return loaiLopList.AsEnumerable();
        }


        public LopSinhVienDTO GetLopSinhVienById(Guid id)
        {
            SqlParameter maLopSV = DBParameterFactory.Create("MaLop", id);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPSelect]", maLopSV);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLopSinhVienDTO();
        }

        public LopSinhVienDTO GetLopSinhVienByKhoaNam(int khoaNam)
        {
            SqlParameter khoaNamParam = DBParameterFactory.Create("KhoaNam", khoaNam);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPSelect]", khoaNamParam);
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToLopSinhVienDTO();
        }

        public IEnumerable<LopSinhVienDTO> GetAllLopSinhVien()
        {
            IList<LopSinhVienDTO> lopSVList = new List<LopSinhVienDTO>();
            SqlParameter maLopSV = DBParameterFactory.Create("MaLop", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LOPSelect]", maLopSV);
            var dataRows = dbResult.Tables[0].Rows;
            foreach (DataRow row in dataRows)
            {
                lopSVList.Add(row.ToLopSinhVienDTO());
            }
            return lopSVList.AsEnumerable();
        }


        public IEnumerable<LopMonHocDTO> GetLopMonHocByIdList(string idList)
        {
            List<LopMonHocDTO> dsLopMonHoc = new List<LopMonHocDTO>();
            SqlParameter idListParam = DBParameterFactory.Create("maLopMonHocList", SqlDbType.VarChar, idList);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_LopMonHocSelectByIdList]", idListParam);
            var dsLopMonHocTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in dsLopMonHocTable)
            {
                dsLopMonHoc.Add(dataRow.ToLopMonHocDTO());
            }
            return dsLopMonHoc.AsEnumerable();
        }
    }
}
